package com.yunyi.integration.action;

import com.yunyi.integration.config.GetConnection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 测试拿到数据库元信息
 *
 * @author yunyi
 */
@RestController
@RequestMapping("/connection")
public class ConnectionAction {

    @Autowired
    private GetConnection getConnection;

    @GetMapping("/getTable")
    public List<String> getTable() {
        List<String> tList = new ArrayList<>();
        try {
            DatabaseMetaData metaData = getConnection.getConnection().getMetaData();
            // 1. 获取表名列表
            String[] types = {"TABLE"};
            //1.1 catalog 类别名称；它必须与存储在数据库中的类别名称匹配；
            // 该参数为 "" 表示获取没有类别的那些描述；为 null 则表示该类别名称不应该用于缩小搜索范围
            //2.2 schemaPattern 数据库名，对于oracle来说就用户名
            //2.3 tableNamePattern 表名称模式；它必须与存储在数据库中的表名称匹配,支持模糊查询，%和_连个占位符，和在数据库中占位符意义一样
            //2.4 types 表的类型(TABLE | VIEW)
            ResultSet rest = metaData.getTables(null, null, "%", types);
            while (rest.next()) {
                String tableName = rest.getString("TABLE_NAME");
                tList.add(tableName);
            }
            //2.获取主键
            for (String tableName : tList) {
                ResultSet resultSet = metaData.getPrimaryKeys(null, null, tableName);
                while (resultSet.next()) {
                    String name = resultSet.getString("COLUMN_NAME");
                    System.out.println("PrimaryKey Column name:" + name);
                }
            }
            //3.获取索引
            for (String tableName : tList) {
                ResultSet resultSet = metaData.getIndexInfo(null, null, tableName,
                        false, false);
                while (resultSet.next()) {
                    String name = resultSet.getString("COLUMN_NAME");
                    System.out.println("Index Column name:" + name);
                }
            }
            //4.获取列信息
            for (String tableName : tList) {
                ResultSet resultSet = metaData.getColumns(null, null, tableName, null);
                while (resultSet.next()) {
                    String name = resultSet.getString("COLUMN_NAME");
                    String type = resultSet.getString("TYPE_NAME");
                    int size = resultSet.getInt("COLUMN_SIZE");
                    System.out.println("Column name: [" + name + "]; type: [" + type + "]; size: [" + size + "]");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tList;
    }


    @GetMapping("/getView")
    public void getView() {
        List<String> tList = new ArrayList<>();
        try {
            DatabaseMetaData metaData = getConnection.getConnection().getMetaData();
            String[] types = {"VIEW"};
            ResultSet rest = metaData.getTables(null, null, "%", types);
            while (rest.next()) {
                String tableName = rest.getString("TABLE_NAME");
                tList.add(tableName);
            }
            for (String tableName : tList) {
                ResultSet resultSet = metaData.getColumns(null, null, tableName, null);
                while (resultSet.next()) {
                    String name = resultSet.getString("COLUMN_NAME");
                    String type = resultSet.getString("TYPE_NAME");
                    int size = resultSet.getInt("COLUMN_SIZE");
                    System.out.println("Column name: [" + name + "]; type: [" + type + "]; size: [" + size + "]");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(tList);
    }

    @GetMapping("/getAll")
    public List<String> getAll() {
        List<String> tList = new ArrayList<>();
        try {
            DatabaseMetaData metaData = getConnection.getConnection().getMetaData();
            String[] types = {"TABLE", "VIEW"};
            ResultSet rest = metaData.getTables(null, null, "%", types);
            while (rest.next()) {
                String tableName = rest.getString("TABLE_NAME");
                tList.add(tableName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tList;
    }

    @GetMapping("/getSearchTable")
    public List<String> getSearchTable() {
        List<String> tList = new ArrayList<>();
        try {
            DatabaseMetaData metaData = getConnection.getConnection().getMetaData();
            String[] types = {"TABLE", "VIEW"};
            ResultSet rest = metaData.getTables(null, null, "%per%", types);
            while (rest.next()) {
                String tableName = rest.getString("TABLE_NAME");
                tList.add(tableName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tList;
    }

}
